package cn.hsa.fqz.common.utils;

import cn.hsa.fqz.common.enums.DataSourceTypeEnums;
import cn.hsa.fqz.common.utils.page.PageData;
import lombok.extern.slf4j.Slf4j;

import java.sql.*;
import java.util.*;

/**
 * @author Administrator
 * @version 1.0
 * @date 2023-11-16 12:10
 */
@Slf4j
public class MySqlExecutor {

    private final static String EX_PARAMS = "?connectTimeout=1000&socketTimeout=3000&autoReconnect=true&useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai";

    public static List<Map<String, Object>> getMapList(String host, Integer port,String databaseName,String userName,String passwd, String sql){
        Connection conn = null;
        Statement st = null;
        try {
            Class.forName(DataSourceTypeEnums.MYSQL.getDriver());
            conn = DriverManager.getConnection(DataSourceTypeEnums.MYSQL.getPrefix()+"://"+host+":"+port +"/"+databaseName + EX_PARAMS,
                    userName, passwd);
            st = conn.createStatement();
            ResultSet rs = st.executeQuery(sql);
            ResultSetMetaData rsmd = rs.getMetaData();
            List<Map<String, Object>> list = new ArrayList<>();
            while (rs.next()) {
                Map<String, Object> row = new TreeMap<>();
                for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                    row.put(rsmd.getColumnName(i), rs.getObject(rsmd.getColumnName(i)));
                }
                list.add(row);
            }
            return list;
        }catch(Exception e){
            log.error(e.getMessage());
        }finally {
            try {
                if(st != null){
                    st.close();
                }
                if(conn != null){
                    conn.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return null;
    }

    public static PageData<Map<String, Object>> getMapPage(String host, Integer port, String databaseName, String userName, String passwd, String sql, int pageNum, int pageSize) {

        PageData<Map<String, Object>> result = new PageData<>(pageNum, pageSize);
        String countSql = getCountSql(sql);
        sql = getPageSql(sql, result);
        log.info("getMapPage==>{}", sql);
        Connection conn = null;
        Statement st = null;
        try  {
            Class.forName(DataSourceTypeEnums.MYSQL.getDriver());
            conn = DriverManager.getConnection(DataSourceTypeEnums.MYSQL.getPrefix()+"://"+host+":"+port +"/"+databaseName + EX_PARAMS,
                    userName, passwd);
            Statement statement = conn.createStatement();
            ResultSet rs = statement.executeQuery(sql);
            ResultSetMetaData rsmd = rs.getMetaData();
            List<Map<String, Object>> list = new ArrayList<>();
            while (rs.next()) {
                Map<String, Object> row = new TreeMap<>();
                for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                    row.put(rsmd.getColumnName(i), rs.getObject(rsmd.getColumnName(i)));
                }
                list.add(row);
            }
            result.setTotal(executeCountSql(host,port,databaseName,userName,passwd,countSql));
            result.setRows(list);
            return result;
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                if(st != null){
                    st.close();
                }
                if(conn != null){
                    conn.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return null;
    }

    private static String getPageSql(String sql, PageData<?> result) {
        int len;
        if ((len = sql.lastIndexOf(";")) == sql.length() - 1) {
            sql = sql.substring(0, len);
        }
        String format = String.format(" limit %d ,%d ", result.getCurrent(), result.getPageSize());
        sql = "SELECT * FROM (" +sql +") a " + format;
        return sql;
    }

    private static String getCountSql(String sql) {
        String newSQL = "";
        int len;
        if ((len = sql.lastIndexOf(";")) == sql.length() - 1) {
            sql = sql.substring(0, len);
        }
        /*if ((len = sql.toUpperCase().indexOf("FROM")) > 0) {
            newSQL = sql.substring(len);
        }*/
        return "SELECT COUNT(1) as count from (" + sql +") a";
    }

    public static long executeCountSql(String host,Integer port,String databaseName,String userName,String passwd,String sql) {
        log.error("脚本统计结果SQL={}",sql);
        Connection conn = null;
        Statement st = null;
        try {
            Class.forName(DataSourceTypeEnums.MYSQL.getDriver());
            conn = DriverManager.getConnection(DataSourceTypeEnums.MYSQL.getPrefix()+"://"+host+":"+port +"/"+databaseName + EX_PARAMS,
                    userName, passwd);
            st = conn.createStatement();
            ResultSet rs = st.executeQuery(sql);
            long count = 0;
            if(rs.next()){
                count = rs.getLong(1);
                if (rs.wasNull()) {
                    count = 0;
                }
            }
            return count;
        } catch (Exception e) {
            log.error("脚本统计结果错误={}", e.getMessage(), e);
        } finally {
            try {
                if(st != null){
                    st.close();
                }
                if(conn != null){
                    conn.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return 0;
    }

    public static long executeSql(String host,Integer port,String databaseName,String userName,String passwd,String sql) {
        log.error("脚本执行结果SQL={}",sql);
        Connection conn = null;
        Statement st = null;
        try {
            Class.forName(DataSourceTypeEnums.MYSQL.getDriver());
            conn = DriverManager.getConnection(DataSourceTypeEnums.MYSQL.getPrefix()+"://"+host+":"+port +"/"+databaseName + EX_PARAMS,
                    userName, passwd);
            st = conn.createStatement();
            boolean flag = st.execute(sql);
            return flag?1:0;
        } catch (Exception e) {
            log.error("脚本统计结果错误={}", e.getMessage(), e);
        }finally {
            try {
                if(st != null){
                    st.close();
                }
                if(conn != null){
                    conn.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return 0;
    }

}
